using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace MacViet.DataBaseAPI
{
    /// <summary>
    /// Description: Concrete DataProvider for table Bill. Provides atomic API functions.
    /// Author:  phong.huynh
    /// Created: 13/08/2010 10:44:22 SA
    /// </summary>
    public class BILLDataAPI
    {
        private SQLHelper _dbTool = new SQLHelper();


        // ************************************
        // Implements function getByColumnName
        // ************************************

        public DataTable getByBILL_ID(System.Int32 BILL_ID)
        {
            SqlParameter[] parm = { new SqlParameter(BILLDTO._BILL_ID, BILL_ID) };
            return _dbTool.executeReader("SP_SEL_BILL_getByBILL_ID", parm);
        }

        public DataTable GetInfoByDay(System.String DAY,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID),
                                      new SqlParameter(BILLDTO._DAY,DAY) };
            return _dbTool.executeReader("SP_GetInfo_By_DAY", parm);
        }        

        public DataTable GetInfoByMonth(System.String MONTH,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID),
                                      new SqlParameter(BILLDTO._MONTH,MONTH) };
            return _dbTool.executeReader("SP_GetInfo_By_MONTH", parm);
        }

        public DataTable GetInfoByYear(System.String YEAR, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID),
                                      new SqlParameter(BILLDTO._YEAR,YEAR) };
            return _dbTool.executeReader("SP_GetInfo_By_YEAR", parm);
        }       
        
        public DataTable GetInfoByQuarter(System.String YEAR, System.Int32 QUARTER, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(BILLDTO._QUARTER,QUARTER),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID)};
            return _dbTool.executeReader("SP_GetInfo_By_Quarter", parm);
        }       
        


        public DataTable getDay(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_DAY",parm);
        }

        public DataTable getbyDay(System.String DAY,System.String MONTH, System.String YEAR,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._DAY,DAY),
                                       new SqlParameter(BILLDTO._MONTH,MONTH),
                                        new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_BY_DAY", parm);
        }

        public DataTable getbyWeek(System.String WEEK, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._WEEK,WEEK),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SELECT_BY_WEEK", parm);
        }

        public DataTable getMonth(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_MONTH",parm);
        }

        public DataTable getbyMonth(System.String MONTH, System.String YEAR, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._MONTH,MONTH),
                                        new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_BY_MONTH", parm);
        }

        public DataTable getbyQuarter(System.String QUARTER, System.String YEAR, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._QUARTER,QUARTER),
                                      new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SELECT_BY_QUARTER", parm);
        }


        public DataTable getYear(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_YEAR",parm);
        }

        public DataTable getbyYear(System.String YEAR, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_BY_YEAR", parm);
        }


        public DataTable getEmpDay(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_EMP_DAY", parm);
        }

        public DataTable getEmpbyDay(System.String DAY, System.String MONTH, System.String YEAR, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._DAY,DAY),
                                       new SqlParameter(BILLDTO._MONTH,MONTH),
                                        new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_GET_EMP_BY_DAY", parm);
        }

        public DataTable getEmpbyWeek(System.String WEEK, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._WEEK,WEEK),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SELECT_EMP_BY_WEEK", parm);
        }

        public DataTable getEmpMonth(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_EMP_MONTH", parm);
        }

        public DataTable getEmpbyMonth(System.String MONTH, System.String YEAR, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._MONTH,MONTH),
                                        new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_GET_EMP_BY_MONTH", parm);
        }

        public DataTable getEmpbyQuarter(System.String QUARTER, System.String YEAR, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._QUARTER,QUARTER),
                                      new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SELECT_EMP_BY_QUARTER", parm);
        }
        public DataTable getEmpYear(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_EMP_YEAR", parm);
        }

        public DataTable getEmpbyYear(System.String YEAR, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {  new SqlParameter(BILLDTO._YEAR,YEAR),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_GET_EMP_BY_YEAR", parm);
        }


        // *************************************************
        // Implement function getAll/getByDynamicCondition
        // *************************************************

        public DataTable getAll()
        {
            return _dbTool.executeReader("SP_SEL_BILL_getAll");
        }

        public DataTable getAllBIll(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_GET_ALL_FROM_BILL",parm);
        }

        public DataTable getAllBIll_DETAIL(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SELECT_BILL_DETAIL",parm);
        }

        public DataTable getAllBIll_SERVICE()
        {
            return _dbTool.executeReader("SP_GET_ALL_FROM_SERVICE");
        }

        public DataTable getTopBIll_Detail(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("sp_getTop1Bill_Detail",parm);
        }

        public DataTable getTopBIll_Ser_Detail(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("sp_getTop1Bill_SEr_Detail", parm);
        }

        // ********************************************************
        // Implement function getByPrimaryKeys / getByForeignKeys
        // In case table have composite primary/foreign keys
        // ********************************************************

        public DataTable getByPrimaryKeys(System.Int32 BILL_ID)
        {
            SqlParameter[] parm = { new SqlParameter(BILLDTO._BILL_ID, BILL_ID) };
            return _dbTool.executeReader("SP_SEL_BILL_getByPrimaryKeys", parm);
        }


        public bool insertBILL(System.Int32 BIL_CUSNAME, System.Decimal BIL_SUBTOTAL, System.Decimal BIL_TAX, System.Decimal BIL_CASHCHAGE, System.Decimal BIL_TOTAL)
        {
            SqlParameter[] parm = { new SqlParameter(BILLDTO._BILL_ID,1),
                                    new SqlParameter(BILLDTO._BIL_CUSNAME,BIL_CUSNAME),
                                    new SqlParameter(BILLDTO._BIL_SUBTOTAL,BIL_SUBTOTAL),
                                    new SqlParameter(BILLDTO._BIL_TAX,BIL_TAX),
                                    new SqlParameter(BILLDTO._BIL_CASHCHAGE,BIL_CASHCHAGE),
                                    new SqlParameter(BILLDTO._BIL_TOTAL,BIL_TOTAL)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_BILL_values", parm);



            return (effectedRows == 1);
        }

        
        public bool InsertToBill(System.String BIL_CUSNAME,System.Double BIL_TOTAL, System.String BIL_DATE, System.String BIL_PAYMENT_TYPE)
        {
            SqlParameter[] parm = {    new SqlParameter("@BIL_CUSNAME", BIL_CUSNAME),
                                   new SqlParameter("@BIL_TOTAL", BIL_TOTAL),
                                   new SqlParameter("@BIL_DATE", BIL_DATE),
                                   new SqlParameter("@BIL_PAYMENT_TYPE", BIL_PAYMENT_TYPE)};
     
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_BILL", parm);

            return (effectedRows == 1);
        }


        public bool Insert_Bill_End(System.Int32 BIL_DETAIL_ID, System.Double BIL_TOTAL, System.Int32 acc_id, System.Int32 APP_ID)
        {
            SqlParameter[] parm = {    new SqlParameter (APPOINTMENTSDTO._APP_ID, APP_ID),
                                      new SqlParameter("@BIL_DETAIL_ID", BIL_DETAIL_ID),
                                   new SqlParameter("@BIL_TOTAL", BIL_TOTAL),
                                   new SqlParameter (ACCOUNTSDTO._ACC_ID, acc_id)};

            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_BILL_OK", parm);

            return (effectedRows == 1);
        }


        public bool Insert_Bill_SER_End(System.Int32 BILL_SER_ID, System.Double TIPS, System.Int32 acc_id)
        {
            SqlParameter[] parm = {   new SqlParameter("@BILL_SER_ID", BILL_SER_ID),                                
                                      new SqlParameter("@TIPS", TIPS),
                                   new SqlParameter (ACCOUNTSDTO._ACC_ID, acc_id)};

            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_BILL_SER_OK", parm);

            return (effectedRows == 1);
        }




        public bool InsertAllToBill_DETAIL(System.Int32 BIL_DETAIL_ID, System.String BIL_CUSNAME, System.Double BIL_AMOUNT, System.DateTime BIL_DATE, System.String BIL_PAYMENT_TYPE,
                                    System.String BIL_PRO_NAME,System.Int32 BIL_QUANTITY,System.Int32 acc_id)
        {
            SqlParameter[] parm = {new SqlParameter("@BIL_DETAIL_ID", BIL_DETAIL_ID),
                                   new SqlParameter("@BIL_CUSNAME", BIL_CUSNAME),
                                   new SqlParameter("@BIL_AMOUNT", BIL_AMOUNT),
                                   new SqlParameter("@BIL_DATE", BIL_DATE),
                                   new SqlParameter("@BIL_PAYMENT_TYPE", BIL_PAYMENT_TYPE),
                                   new SqlParameter("@BIL_PRO_NAME", BIL_PRO_NAME),
                                   new SqlParameter("@BIL_QUANTITY", BIL_QUANTITY),
                                   new SqlParameter(ACCOUNTSDTO._ACC_ID, acc_id)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_INTO_BILL_DETAIL", parm);

            return (effectedRows == 1);
        }

        public bool InsertTipsToBill_SERVICE(System.Double TIPS)
        {
            SqlParameter[] parm = {new SqlParameter(BILLDTO._TIPS, TIPS)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_TIP_TO_BILL_SERVICE", parm);

            return (effectedRows == 1);
        }


        public bool Insert_TO_BILL(System.Int32 BIL_DETAIL_ID,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(BILLDTO._BIL_DETAIL_ID,BIL_DETAIL_ID),
                                    new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INSERT_INTO_BILL", parm);

            return (effectedRows == 1);
        }


        public bool InsertToBill_SER_DETAIL(System.Int32 APP_ID,System.Int32 ACC_ID, System.Int32 BILL_SER_ID, System.String BILL_SER_NAME,System.Double BILL_SER_PRICE,System.DateTime BILL_SER_DATE,System.Int32 EMP_ID)
        {
            SqlParameter[] parm = {new SqlParameter(BILLDTO._APP_ID,APP_ID),
                                      new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                   new SqlParameter(BILLDTO._BILL_SER_ID,BILL_SER_ID),
                                   new SqlParameter(BILLDTO._BILL_SER_NAME,BILL_SER_NAME),
                                   new SqlParameter(BILLDTO._BILL_SER_PRICE,BILL_SER_PRICE),
                                   new SqlParameter(BILLDTO._BILL_SER_DATE,BILL_SER_DATE),
                                  new SqlParameter(EMPLOYEESDTO._EMP_ID,EMP_ID)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_INTO_BILL_SER_DETAIL", parm);

            return (effectedRows == 1);
        }        

        ///<summary>		
        /// Thu+.c hie^.n ca^u le^.nh update du+.a tre^n kho'a chi'nh
        ///</summary>

        public bool updateBILL(System.Int32 BILL_ID, System.Int32 BIL_CUSNAME, System.Decimal BIL_SUBTOTAL, System.Decimal BIL_TAX, System.Decimal BIL_CASHCHAGE, System.Decimal BIL_TOTAL)
        {
            SqlParameter[] parm = { new SqlParameter(BILLDTO._BILL_ID,BILL_ID),
                                    new SqlParameter(BILLDTO._BIL_CUSNAME,BIL_CUSNAME),
                                    new SqlParameter(BILLDTO._BIL_SUBTOTAL,BIL_SUBTOTAL),
                                    new SqlParameter(BILLDTO._BIL_TAX,BIL_TAX),
                                    new SqlParameter(BILLDTO._BIL_CASHCHAGE,BIL_CASHCHAGE),
                                    new SqlParameter(BILLDTO._BIL_TOTAL,BIL_TOTAL)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_BILL_update", parm);

            return affectRows == 1;
        }


        // ********************************************************************************
        // Implementation of deleteAll/deteleByForeignKeys/deleteByPrimaryKeys function		
        // ********************************************************************************

        // return number of deleted rows		
        public int deleteAll()
        {
            return _dbTool.ExecuteNoneQuery("SP_DEL_BILL_deleteAll");
        }

        public int Delete_All_CART(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.ExecuteNoneQuery("SP_CART_DELETE_ALL",parm);
        }


        public int Delete_All_BILL_SERVICE()
        {
            return _dbTool.ExecuteNoneQuery("SP_DETELE_BILL_SERVICE");
        }
        
        



        // return number of deleted rows
        public int deleteByPrimaryKeys(System.Int32 BILL_ID)
        {
            SqlParameter[] parm = { new SqlParameter(BILLDTO._BILL_ID, BILL_ID) };
            return _dbTool.ExecuteNoneQuery("SP_DEL_BILL_deleteByPrimaryKeys", parm);
        }

    }
}